/**
 * 
 */
package com.wolfpire.proms.dao.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.wolfpire.proms.dao.FeedBackDao;
import com.wolfpire.proms.dao.TaskDao;
import com.wolfpire.proms.model.FeedBack;
import com.wolfpire.system.common.Constants;
import com.wolfpire.system.common.Page;
import com.wolfpire.system.common.base.dao.impl.BaseHibernateDao;

/**
 * @author lihd
 *
 */
@Repository("feedBackDao")
public class FeedBackDaoImpl extends BaseHibernateDao<FeedBack, Long> implements
		FeedBackDao {
	
	@Resource private TaskDao taskDao;

	@Override
	public Page<FeedBack> findFeedBacks(Page<FeedBack> page,
			FeedBack filterFeedBack) {
		Criterion[] criterions = this.createFilter(filterFeedBack);
		int totalCount = this.findIntByCriteria(criterions);
		page.setTotalCount(totalCount);
		
		if (0 < totalCount) {
			Criteria c = this.createCriteria(criterions);
			c.addOrder(Order.desc("id"));
			c = this.setPageParameter(c, page);
			@SuppressWarnings("unchecked")
			List<FeedBack> result = c.list();
			page.setDataList(result);
		}
		return page;
	}
	
	@Override
	public Page<FeedBack> findFeedBacks(Page<FeedBack> page,
			FeedBack filterFeedBack, List<Order> orders) {
		Criterion[] criterions = this.createFilter(filterFeedBack);
		
		int totalCount = this.findIntByCriteria(criterions);
		page.setTotalCount(totalCount);
		if (0 < totalCount) {
			Criteria c = this.createCriteria(criterions);
			if (CollectionUtils.isNotEmpty(orders)) {
				for (Order o : orders) {
					c.addOrder(o);
				}
			} else {
				c.addOrder(Order.desc("id"));
			}
			c = this.setPageParameter(c, page);
			@SuppressWarnings("unchecked")
			List<FeedBack> result = c.list();
			page.setDataList(result);
		}
		return page;
	}

	private Criterion[] createFilter(FeedBack filterFeedBack) {
		if (null == filterFeedBack) {
			return null;
		}
		List<Criterion> criterions = new ArrayList<Criterion>();
		if (null != filterFeedBack.getId()) {
			criterions.add(Restrictions.eq("id", filterFeedBack.getId()));
		}
		if (!StringUtils.isBlank(filterFeedBack.getName())) {
			criterions.add(Restrictions.like("name", filterFeedBack.getName(), MatchMode.START));
		}
		if (!StringUtils.isBlank(filterFeedBack.getRemark())) {
			criterions.add(Restrictions.like("remark", filterFeedBack.getRemark(), MatchMode.ANYWHERE));
		}
		if (null != filterFeedBack.getTaskId()) {
			criterions.add(Restrictions.eq("taskId", filterFeedBack.getTaskId()));
		}
		if (null != filterFeedBack.getProjectIndexId()) {
			List<Long> taskIds = this.taskDao.getAllTaskIdsByProjectIndexId(filterFeedBack.getProjectIndexId());
			criterions.add(Restrictions.in("taskId", taskIds));
		}
		if (null != filterFeedBack.getCreateUserId()) {
			criterions.add(Restrictions.eq("createUserId", filterFeedBack.getCreateUserId()));
		}
		if(null != filterFeedBack.getCreateUserName()){
			criterions.add(Restrictions.like("createUserName", filterFeedBack.getCreateUserName(), MatchMode.ANYWHERE));
		}
		if (null != filterFeedBack.getDelFlag()) {
			criterions.add(Restrictions.eq("delFlag", filterFeedBack.getDelFlag()));
		}
		if (null != filterFeedBack.getStatus()) {
			criterions.add(Restrictions.eq("status", filterFeedBack.getStatus()));
		}
		if(filterFeedBack.getExcTimeBegin()!=null){
			criterions.add(Restrictions.ge("executeTime", filterFeedBack.getExcTimeBegin()));
		}
		if(filterFeedBack.getExcTimeEnd()!=null){
			criterions.add(Restrictions.le("executeTime", filterFeedBack.getExcTimeEnd()));
		}
		
		if(filterFeedBack.getCreateTimeBegin()!=null){
			criterions.add(Restrictions.ge("createTime", filterFeedBack.getCreateTimeBegin()));
		}
		if(filterFeedBack.getCreateTimeEnd()!=null){
			criterions.add(Restrictions.le("createTime", filterFeedBack.getCreateTimeEnd()));
		}
		
		return criterions.toArray(new Criterion[criterions.size()]);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<FeedBack> list(FeedBack feedBack) {
		Criterion[] criterions = this.createFilter(feedBack);
		Criteria c = this.createCriteria(criterions);
		return c.list();
	}

	@Override
	public String countFactAmountByTaskId(Long taskId) {
		StringBuffer querySql = new StringBuffer();
//		querySql.append(" SELECT SUM(IFNULL(fact_amount, '0.00') + 0.00) FROM t_feedback WHERE task_id=?  AND del_flag=? AND status=?");
		querySql.append(" SELECT SUM(IFNULL(f.fact_amount, '0.00') + 0.00) FROM t_feedback f, t_task_plan tp, t_task t ")
				.append(" WHERE f.plan_id = tp.id AND tp.task_id = t.id  AND tp.del_flag=1 AND t.id=? AND f.del_flag=?  AND f.status=? ");
		Object rs = this.findUniqueBySql(querySql.toString(), new Object[]{taskId, Constants.NORMAL_FLAG, Constants.FEEDBACK_AUDIT_AGREE});
		if (null != rs) {
			BigDecimal value = new BigDecimal(rs.toString()).setScale(2, 1);
			return value.toString();
		}
		return "0.00";
	}

	@Override
	public String findLastProgressByTaskId(Long taskId) {
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT IFNULL(progress, '0.00') FROM t_feedback WHERE task_id=? AND del_flag=? ORDER BY create_time DESC LIMIT 1 ");
		Object rs = this.findUniqueBySql(querySql.toString(), new Object[]{taskId, Constants.NORMAL_FLAG});
		if (null != rs) {
			return rs.toString();
		}
		return "0.00";
	}
	
	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public Map<String, Object> countWithPlan(Long taskId) {
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT ( ")
			.append(" SELECT COUNT(1) FROM t_feedback f, t_task_plan p ")
			.append(" WHERE f.plan_id = p.id AND p.task_id =:taskId1 AND f.`status` = '3' ")
			.append(" ) AS done_plan, ")
			.append(" (SELECT COUNT(1) FROM t_task_plan p WHERE p.task_id =:taskId2) AS all_plan ");
		List<String> propertyNames = new ArrayList<String>();
		propertyNames.add("taskId1");
		propertyNames.add("taskId2");
		List propertyValues = new ArrayList();
		propertyValues.add(taskId);
		propertyValues.add(taskId);
		SQLQuery query = this.createSqlQuery(querySql.toString(), propertyNames , propertyValues );
		return (Map<String, Object>) query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).uniqueResult();
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public Page<Map<String, Object>> findAuditedList(Page<Map<String, Object>> page, FeedBack filterFeedBack) {
		StringBuffer querySqlBuffer = new StringBuffer();
		StringBuffer countSqlBuffer = new StringBuffer();
		List<String> propertyNames = new ArrayList<String>();
		List propertyValues = new ArrayList();
		querySqlBuffer.append(" SELECT ")
				.append(" f.id, t.id as task_id, tp.id as task_plan_id, ")
				.append(" f.`name`, t.`name` as task_name, tp.`name` as task_plan_name, ")
				.append("  f.pre_amount, f.fact_amount, f.create_user_name, f.audit_user_name,  ")
				.append(" DATE_FORMAT( f.execute_time, '%Y-%m-%d' ) as execute_time, ")
				.append(" DATE_FORMAT( f.audti_time, '%Y-%m-%d' ) as audti_time, ")
				.append(" DATE_FORMAT( f.create_time, '%Y-%m-%d' ) as create_time ")
				.append(" FROM t_feedback f  ")
				.append(" LEFT JOIN t_task t ON f.task_id = t.id AND t.del_flag = 1 ")
				.append(" LEFT JOIN t_task_plan tp  ON f.plan_id = tp.id AND tp.del_flag = 1 ")
				.append(" WHERE f.del_flag = 1 AND f.`status` = 3 ");
		if (null != filterFeedBack.getExcTimeBegin()) {
			querySqlBuffer.append(" and f.execute_time >=:excTimeBegin ");
			propertyNames.add("excTimeBegin");
			propertyValues.add(filterFeedBack.getExcTimeBegin());
		}				
		if (null != filterFeedBack.getExcTimeEnd()) {
			querySqlBuffer.append(" and f.execute_time <=:excTimeEnd ");
			propertyNames.add("excTimeEnd");
			propertyValues.add(filterFeedBack.getExcTimeEnd());
		}
		if (null != filterFeedBack.getCreateTimeBegin()) {
			querySqlBuffer.append(" and f.create_time >=:createTimeBegin ");
			propertyNames.add("createTimeBegin");
			propertyValues.add(filterFeedBack.getCreateTimeBegin());
		}
		if (null != filterFeedBack.getCreateTimeEnd()) {
			querySqlBuffer.append(" and f.create_time <=:createTimeEnd ");
			propertyNames.add("createTimeEnd");
			propertyValues.add(filterFeedBack.getCreateTimeEnd());
		}
		if (null != filterFeedBack.getProjectIndexId()) {
			List<Long> taskIds = this.taskDao.getAllTaskIdsByProjectIndexId(filterFeedBack.getProjectIndexId());
			querySqlBuffer.append(" and f.task_id in (:taskIds) ");
			propertyNames.add("taskIds");
			propertyValues.add(taskIds);
		}
		countSqlBuffer.append(" SELECT count(1) from (").append(querySqlBuffer).append(") AS COUNT ");
		int totalCount = this.findSqlInt(countSqlBuffer.toString(), propertyNames, propertyValues);
		page.setTotalCount(totalCount);
		querySqlBuffer.append(" ORDER BY f.create_time DESC");
		if (0 < totalCount) {
			List<Map<String, Object>> list = this.setPageParameter(page, querySqlBuffer.toString(), propertyNames, propertyValues);
			page.setDataList(list);
		}
		return page;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public List<Map<String, Object>> findAuditedList(FeedBack filterFeedBack) {
		StringBuffer querySqlBuffer = new StringBuffer();
		List<String> propertyNames = new ArrayList<String>();
		List propertyValues = new ArrayList();
		querySqlBuffer.append(" SELECT ")
				.append(" f.id, t.id as task_id, tp.id as task_plan_id, ")
				.append(" f.`name`, t.`name` as task_name, tp.`name` as task_plan_name, ")
				.append("  f.pre_amount, f.fact_amount, f.create_user_name, f.audit_user_name,  ")
				.append(" DATE_FORMAT( f.execute_time, '%Y-%m-%d' ) as execute_time, ")
				.append(" DATE_FORMAT( f.audti_time, '%Y-%m-%d' ) as audti_time, ")
				.append(" DATE_FORMAT( f.create_time, '%Y-%m-%d' ) as create_time ")
				.append(" FROM t_feedback f  ")
				.append(" LEFT JOIN t_task t ON f.task_id = t.id AND t.del_flag = 1 ")
				.append(" LEFT JOIN t_task_plan tp  ON f.plan_id = tp.id AND tp.del_flag = 1 ")
				.append(" WHERE f.del_flag = 1 AND f.`status` = 3 ");
		if (null != filterFeedBack.getExcTimeBegin()) {
			querySqlBuffer.append(" and f.execute_time >=:excTimeBegin ");
			propertyNames.add("excTimeBegin");
			propertyValues.add(filterFeedBack.getExcTimeBegin());
		}				
		if (null != filterFeedBack.getExcTimeEnd()) {
			querySqlBuffer.append(" and f.execute_time <=:excTimeEnd ");
			propertyNames.add("excTimeEnd");
			propertyValues.add(filterFeedBack.getExcTimeEnd());
		}
		if (null != filterFeedBack.getCreateTimeBegin()) {
			querySqlBuffer.append(" and f.create_time >=:createTimeBegin ");
			propertyNames.add("createTimeBegin");
			propertyValues.add(filterFeedBack.getCreateTimeBegin());
		}
		if (null != filterFeedBack.getCreateTimeEnd()) {
			querySqlBuffer.append(" and f.create_time <=:createTimeEnd ");
			propertyNames.add("createTimeEnd");
			propertyValues.add(filterFeedBack.getCreateTimeEnd());
		}
		if (null != filterFeedBack.getProjectIndexId()) {
			List<Long> taskIds = this.taskDao.getAllTaskIdsByProjectIndexId(filterFeedBack.getProjectIndexId());
			querySqlBuffer.append(" and f.task_id in (:taskIds) ");
			propertyNames.add("taskIds");
			propertyValues.add(taskIds);
		}
		querySqlBuffer.append(" ORDER BY f.create_time DESC");
		SQLQuery query = this.createSqlQuery(querySqlBuffer.toString(), propertyNames, propertyValues);
		List<Map<String, Object>> list = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		return list;
	}
	
	
}
